﻿using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using TTQT.TTQT_Code;
public class CommonDAO
{
    /// <summary>
    /// Hàm này chạy nonequery. Trả về số bản ghi ảnh hưởng
    /// </summary>
    /// <param name="selectStatement"></param>
    /// <returns></returns>
    public int chayCauUpdate(string selectStatement)
    {
        int kq = 0;
        SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
        SqlCommand cmd = new SqlCommand(selectStatement, conn);
        cmd.CommandType = CommandType.Text;
        try
        {
            conn.Open();
            kq = TTQTCommon.ToInt32(cmd.ExecuteNonQuery(), -1);
        }
        catch (Exception ex)
        { }
        finally
        {
            if (conn.State == ConnectionState.Open) conn.Close();
            conn.Dispose();
            cmd.Dispose();
        }
        return kq;
    }

    /// <summary>
    /// Hàm này scalar Trả về giá trị int duy nhất
    /// </summary>
    /// <param name="selectStatement"></param>
    /// <returns></returns>
    public int chayCauSQLScalarInt(string selectStatement)
    {
        int kq = 0;
        SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
        SqlCommand cmd = new SqlCommand(selectStatement, conn);
        cmd.CommandType = CommandType.Text;
        try
        {
            conn.Open();
            kq = TTQTCommon.ToInt32(cmd.ExecuteScalar(), -1);
        }
        catch (Exception ex)
        { }
        finally
        {
            if (conn.State == ConnectionState.Open) conn.Close();
            conn.Dispose();
            cmd.Dispose();
        }
        return kq;
    }
    public decimal chayCauSQLScalarDecimal(string selectStatement)
    {
        decimal kq = 0;
        SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
        SqlCommand cmd = new SqlCommand(selectStatement, conn);
        cmd.CommandType = CommandType.Text;
        try
        {
            conn.Open();
            kq = TTQTCommon.ToDecimal(cmd.ExecuteScalar(), -1);
        }
        catch (Exception ex)
        { }
        finally
        {
            if (conn.State == ConnectionState.Open) conn.Close();
            conn.Dispose();
            cmd.Dispose();
        }
        return kq;
    }
    public string chayCauSQLScalarString(string selectStatement)
    {
        string kq = "";
        SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
        SqlCommand cmd = new SqlCommand(selectStatement, conn);
        cmd.CommandType = CommandType.Text;
        try
        {
            conn.Open();
            kq = TTQTCommon.ToString(cmd.ExecuteScalar().ToString());
        }
        catch (Exception ex)
        { }
        finally
        {
            if (conn.State == ConnectionState.Open) conn.Close();
            conn.Dispose();
            cmd.Dispose();
        }
        return kq;
    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="commandType">s - store; t- text</param>
    /// <returns></returns>
    public DataTable queryForTable(string sql,string commandType)
    {
        DataTable result = null;
        SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
        SqlCommand comm = new SqlCommand(sql, conn);
        if (commandType.ToLower().Equals("t"))
            comm.CommandType = CommandType.Text;
        else
            comm.CommandType = CommandType.StoredProcedure;
        try
        {
            result = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            da.Fill(result);
        }
        catch (Exception exx)
        {
            // Writing log methods here.
        }
        finally
        {
            if (conn.State == ConnectionState.Open) conn.Close();
            conn.Dispose();
            comm.Dispose();
        }
        return result;
    }

    public DataTable sanPham_Nhom_getAllGroup()
    {
        DataTable result = null;
        SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
        SqlCommand comm = new SqlCommand("sanPham_Nhom_getAllGroup", conn);
        comm.CommandType = CommandType.StoredProcedure;
        
        try
        {
            conn.Open();
            result = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            da.Fill(result);
        }
        catch (Exception exx)
        {
            // Writing log methods here.
        }
        finally
        {
            if (conn.State == ConnectionState.Open) conn.Close();
            conn.Dispose();
            comm.Dispose();
        }
        return result;
    }
    public DataTable sanPham_NhomGetForSearchNhom(string parentId)
    {
        DataTable result = null;
        SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
        SqlCommand comm = new SqlCommand("sanPham_Nhom_GetForSearchNhom", conn);
        comm.CommandType = CommandType.StoredProcedure;
        comm.Parameters.AddWithValue("@parentId", parentId);
        try{
            result = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            da.Fill(result);
        }
        catch (Exception exx)
        {
            // Writing log methods here.
        }
        finally
        {
            if (conn.State == ConnectionState.Open) conn.Close();
            conn.Dispose();
            comm.Dispose();
        }
        return result;
    }

    public void SanPham_ToggleColumnValue(string colName,string id, string value)
    {
        SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
        string sql = "", giaTri = "";
        if (value.Equals("1"))
        {
            giaTri = "0";
        }
        else
        {
            giaTri = "1";
        }
        sql = "Update SanPham set " + colName  +"= " + giaTri + " where id=" + id;
        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.CommandType = CommandType.Text;
        try
        {
            conn.Open();
            cmd.ExecuteNonQuery();
        }
        catch
        { }
        finally
        {
            if (conn.State == ConnectionState.Open) conn.Close();
            conn.Dispose();
            cmd.Dispose();
        }
    }

    public int LogNgoaiLe_Insert(string userName, string thaotac, string IP, string Loi)
    {
        int kq = 0;
        SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
        SqlCommand cmd = new SqlCommand("LogNgoaiLe_Insert", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@userName", userName);
        cmd.Parameters.AddWithValue("@thaotac", thaotac);
        cmd.Parameters.AddWithValue("@IP", IP);
        cmd.Parameters.AddWithValue("@Loi", Loi);
        try
        {
            conn.Open();
            kq = TTQTCommon.ToInt32(cmd.ExecuteScalar(), 0);
        }
        catch (Exception exxx)
        { }
        finally
        {
            if (conn.State == ConnectionState.Open) conn.Close();
            conn.Dispose();
            cmd.Dispose();
        }
        return kq;
    }

    public DataTable LogNgoaiLe_selectAll()
    {
        DataTable result = null;
        string sql = "";
        sql = "LogNgoaiLe_selectAll";
        SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
        SqlCommand comm = new SqlCommand(sql, conn);
        comm.CommandType = CommandType.StoredProcedure;
        try
        {
            result = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            da.Fill(result);
        }
        catch (Exception exx)
        {
            // Writing log methods here.
        }
        finally
        {
            if (conn.State == ConnectionState.Open) conn.Close();
            conn.Dispose();
            comm.Dispose();
        }
        return result;
    }
    public void logNgoaiLe_truncate(string selectStatement)
    {
      
        SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
        SqlCommand cmd = new SqlCommand("logNgoaiLe_truncate", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        try
        {
            conn.Open();
            cmd.ExecuteNonQuery().ToString();
        }
        catch (Exception ex)
        { }
        finally
        {
            if (conn.State == ConnectionState.Open) conn.Close();
            conn.Dispose();
            cmd.Dispose();
        }
    }
    public void logIPView(string ip)
    {
        SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
        SqlCommand cmd = new SqlCommand("logIPView_Add", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@IP", ip);
        try
        {
            conn.Open();
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        { }
        finally
        {
            if (conn.State == ConnectionState.Open) conn.Close();
            conn.Dispose();
            cmd.Dispose();
        }
    }
}
